import gdata.spreadsheet.text_db 
import gdata.docs.client
import gdata.acl.data
import random
import time

class database:
  """Google Docs database interface for Poznamkovy Blok project"""
  
  def __init__(self, username='cvutpoznamky@gmail.com', password='osklivyheslo'):
    self.username = username
    self.client = gdata.spreadsheet.text_db.DatabaseClient(username, password)
    
    self.fileClient = gdata.docs.client.DocsClient(source='poznBlok-v1') 
    self.fileClient.ClientLogin(username, password, self.fileClient.source);
    self.fileClient.ssl = True  # Force all API requests through HTTPS
    self.fileClient.http_client.debug = False  # Set to True for debugging HTTP requests
    
  def createNewBook(self, book_name, author='N/A', isbn='N/A', descr='N/A', genre='N/A', tags='', cretimestamp='', modtimestamp='', creuser='', moduser=''):
    """Create new book and set its name. Sheet on googleDOCS is in format DB_<hash_num>.
      Args:  book_name - book name
      [author] - default 'N/A'
      [isbn] - default 'N/A'
      [descr] - default 'N/A'
			[genre] - default 'N/A'
			[tags] - default ''
      [cretimestamp] - default '', inserts time.time()
      [modtimestamp] - default '', inserts time.time()
      [creuser] - default '', inserts username
      [moduser] - default '', inserts username
      Returns: new book unique identifier, -1 if error
    """
    #if self.client.GetDatabases(name="DB_"+book_name):
    #  return False
          
    if book_name == "" or len(book_name) > 30:
      return -1
  
    if cretimestamp is '':
      cretimestamp = str(time.time())
      
    if modtimestamp is '':
      modtimestamp = str(time.time())
      
    if creuser is '':
      creuser = self.username
      
    if moduser is '':
      moduser = self.username
    
    genDbName = "DB_"+str(abs(hash(book_name+str(random.randint(1,1000000))))) # Generate pseudorandom database name, format "DB_<hash_num>"
    tmpDB = self.client.CreateDatabase(genDbName)
    tmpTableDescr = tmpDB.CreateTable('descr', ['name', 'author', 'cretimestamp','isbn', 'descr', 'genre', 'tags', 'cnt',  'modtimestamp', 'creuser', 'moduser'])
    tmpTableNote = tmpDB.CreateTable('note', ['id', 'header', 'body', 'tags', 'cretimestamp', 'modtimestamp', 'creuser', 'moduser'])
    tmpRecord = tmpTableDescr.AddRecord({'name':book_name, 'author':author, 'isbn':isbn, 'descr':descr, 'genre':genre, 'tags':tags, 'cnt':'0', 'cretimestamp':cretimestamp, 'modtimestamp':modtimestamp, 'creuser':creuser, 'moduser':moduser})
   
    return tmpDB.spreadsheet_key
  
  def listBooks(self, book_name=None):
    """Returns a collection of available books. Result set can be filtered by name (partial match).
      Arg: [book_name] - name filter
      Returns: list of dictionaries {'id', 'name', author', 'isbn', 'descr', 'genre', 'tags', 'cretimestamp', 'modtimestamp', 'creuser', 'moduser'}, where ID = Google Docs spreadsheet identifier
    """
    DB_list = self.client.GetDatabases(name="DB_") # get all databases
    books = []
    for book in DB_list: # create list of dictionaries containging book info
      row_info = book.GetTables(name='descr')[0].GetRecord(row_number='1')
      if book_name is not None:
        if book_name in row_info.content['name']: # the 'in' function for partial match search
          info = {'id':book.spreadsheet_key, 'name': row_info.content['name'], 'author':row_info.content['author'], 'isbn':row_info.content['isbn'], 'descr':row_info.content['descr'], 'genre':row_info.content['genre'], 'tags':row_info.content['tags'], 'cretimestamp':row_info.content['cretimestamp'], 'modtimestamp':row_info.content['modtimestamp'], 'creuser':row_info.content['creuser'], 'moduser':row_info.content['moduser']}
          books.append(info)
      else:	  
        info = {'id':book.spreadsheet_key, 'name': row_info.content['name'], 'author':row_info.content['author'], 'isbn':row_info.content['isbn'], 'descr':row_info.content['descr'], 'genre':row_info.content['genre'], 'tags':row_info.content['tags'], 'cretimestamp':row_info.content['cretimestamp'], 'modtimestamp':row_info.content['modtimestamp'], 'creuser':row_info.content['creuser'], 'moduser':row_info.content['moduser']}
        books.append(info)
    return books
    
  def deleteBook(self, book_id):
    """Deletes book from Google Docs.
      Arg: book_id - Google Docs spreadsheet identifier
      Returns: boolean success
    """
    try:
      DB_list = self.client.GetDatabases(spreadsheet_key=book_id)
    except:
      return False
    if(len(DB_list)) != 1:                
      return False
    DB_list[0].Delete()
    return True
	
  def updateBook(self, book_id, name=None, author=None, isbn=None, descr=None, genre=None, tags=None, modtimestamp=None, moduser=None):
    """Updates book info.
  	  Args: book_id - Google Docs spreadsheet identifier
  			[name] - new book name
        [author]
  			[isbn]
  			[descr]
  			[genre]
  			[tags]
        [modtimestamp]
        [moduser]
  	  Returns: boolean success	
    """
    if name=="" or len(name) > 30:
      return False
      
    DB_list = self.client.GetDatabases(spreadsheet_key=book_id) 
    if(len(DB_list)) != 1:
      return False
  
    tables = DB_list[0].GetTables()
    if(len(tables)) != 3:
      return False
    
    row_book_info = tables[1].GetRecord(row_number='1')
  
    modified = False
    if name is not None and name != "":
      row_book_info.content['name'] = name
      modified = True
  	
    if author is not None:
      row_book_info.content['author'] = author
      modified = True

    if isbn is not None:
      row_book_info.content['isbn'] = isbn
      modified = True

    if genre is not None:
      row_book_info.content['genre'] = genre
      modified = True
  	  
    if descr is not None:
      row_book_info.content['descr'] = descr
      modified = True
  	  
    if tags is not None:
      row_book_info.content['tags'] = tags
      modified = True
      
    if modified or moduser is not None or modtimestamp is not None:
      if moduser is not None:
        row_book_info.content['moduser'] = moduser
      else:
        row_book_info.content['moduser'] = self.username
        
      if modtimestamp is not None:
        row_book_info.content['modtimestamp'] = str(modtimestamp)
      else:
        row_book_info.content['modtimestamp'] = str(time.time())
      
      modified = True        
  	
    if modified:
      row_book_info.Push()
  	  
    return modified


  def createNote(self, book_id, header="N/A", body="N/A", tags="", cretimestamp=None, modtimestamp=None, creuser=None, moduser=None):
    """Creates note for selected book.
      Args: book_id - Google Docs spreadsheet identifier
            [header] - note header
            [body] - body input
            [tags] - tags divided by comma
            [cretimestamp] - creation timestamp (date.date())
            [modtimestamp] - modification timestamp (date.date())
            [creuser] - creator
            [moduser] - modificator
      Return: boolean success
    """
    try:
      DB_list = self.client.GetDatabases(spreadsheet_key=book_id)
      if(len(DB_list)) != 1:
        raise
        
      tables = DB_list[0].GetTables()
      if(len(tables)) != 3:
        raise
        
      row_book_info = tables[1].GetRecord(row_number='1')
      new_note_pk = int(row_book_info.content['cnt']) # get new primary key (id) for the note
      row_book_info.content['cnt'] = str(new_note_pk + 1) # move note primary key pointer
      row_book_info.Push() # and update this row
      # tables[2] is table 'note', fill in the new one.
      tmpRecord = tables[2].AddRecord({'id':str(new_note_pk), 'header':header, 'body':body, 'tags':tags}) 
      
      if cretimestamp is not None:
        tmpRecord.content['cretimestamp'] = str(cretimestamp)
      else:
        tmpRecord.content['cretimestamp'] = str(time.time())
        
      if modtimestamp is not None:
        tmpRecord.content['modtimestamp'] = str(modtimestamp)
      else:
        tmpRecord.content['modtimestamp'] = str(time.time())
        
      if creuser is not None:
        tmpRecord.content['creuser'] = creuser
      else:
        tmpRecord.content['creuser'] = self.username
        
      if moduser is not None:
        tmpRecord.content['moduser'] = moduser
      else:
        tmpRecord.content['moduser'] = self.username
      
      tmpRecord.Push()
      return str(new_note_pk)
    except:
      return "-1"
  
  def getNotesByBookID(self, book_id):
    """Returns a collection of notes stored for selected book.
      Arg: book_id - Google Docs spreadsheet identifier
      Returns: list of dictionaries - dictionary contains keys: id, header, body, tags, cretimestamp, modtimestamp, creuser, moduser
    """
    DB_list = self.client.GetDatabases(spreadsheet_key=book_id) 
    if(len(DB_list)) != 1:
      return []
    tables = DB_list[0].GetTables()
    if(len(tables)) != 3:
      return []                                                                                         
    # note_rows is RecordResultSet datatype, child of List datatype.
    note_rows = tables[2].GetRecords(start_row=1, end_row=1048576) # gets first 2^20 notes, hope this is enough :]
    notes = []
    while True:   # do while realization 
      while len(note_rows):
        note_row = note_rows.pop(0) # get first row from list of rows
        note = {'id':note_row.content['id'], 'header':note_row.content['header'], 'body':note_row.content['body'], 'tags':note_row.content['tags'], 'cretimestamp':note_row.content['cretimestamp'], 'modtimestamp':note_row.content['modtimestamp'], 'creuser':note_row.content['creuser'], 'moduser':note_row.content['moduser']}
        notes.append(note)         
      note_rows = note_rows.GetNext()
      if note_rows is None: # the do while end condition
        break   
    return notes
    
  def updateNote(self, book_id, note_id, header=None, body=None, tags=None, modtimestamp=None, moduser=None):
    """Update book note defined by id.
      Args: book_id - Google Docs spreadsheet identifier, represents book containing the note
            note_id - note identificator
            [header] - note data
            [body] - note data
            [tags] - note data
            [modtimestamp] - modification timestamp
            [moduser] - modificator
      Returns: boolean success    
    """
    DB_list = self.client.GetDatabases(spreadsheet_key=book_id) 
    if(len(DB_list)) != 1:
      return False
    
    tables = DB_list[0].GetTables()
    if(len(tables)) != 3:
      return False
    
    rows_feed = tables[2].FindRecords('id == ' + note_id) # get the updated row
    if(len(rows_feed)) != 1:
      return False
    
    row = rows_feed[0] # row to be updated
    modified = False # update only changed data
    if header is not None:
      row.content['header'] = header
      modified = True
    if body is not None:
      row.content['body'] = body
      modified = True
    if tags is not None:
      row.content['tags'] = tags
      modified = True
    
    if modified:                        
      if modtimestamp is not None:
        row.content['modtimestamp'] = str(modtimestamp)
      else:
        row.content['modtimestamp'] = str(time.time())
        
      if moduser is not None:
        row.content['moduser'] = moduser
      else:
        row.content['moduser'] = self.username
      
      row.Push() # commit data to server
      
    return modified
	    
  def deleteNote(self, book_id, note_id):
    """Delete note by id
      Args: book_id - Google Docs spreadsheet identifier
            note_id - note to be deleted
      Returns: boolean success    
    """
    DB_list = self.client.GetDatabases(spreadsheet_key=book_id) 
    if(len(DB_list)) != 1:
      return False
    
    tables = DB_list[0].GetTables()
    if(len(tables)) != 3:
      return False
    
    rows_feed = tables[2].FindRecords('id == ' + note_id) # get the deleted row
    if(len(rows_feed)) != 1:
      return False
    
    rows_feed[0].Delete() # delete the row
    return True
    
#   def createBookShare(self, book_id, target_user):
#     """Creates or modifies share to user defined by target_user with writing permissions.
#       Args: book_id - Google Docs spreadsheet identifier
#             target_user - email of target GoogleDOCS account
#       Returns : boolean success      
#     """
#     try:                 
#       acl_feed = self.fileClient.GetAclPermissions('spreadsheet:'+book_id)
#       for acl in acl_feed.entry:
#         if (acl.scope.value == target_user):
#           return False
#       
#       scope = gdata.acl.data.AclScope(value=target_user, type='user')
#       role = gdata.acl.data.AclRole(value='writer')
#       acl_entry = gdata.docs.data.Acl(scope=scope, role=role)
#       
#       doc_entry = self.fileClient.GetDoc('spreadsheet:'+book_id)
#       new_acl = self.fileClient.Post(acl_entry, doc_entry.GetAclFeedLink().href)
#       return True
#     except e:
#       return False
#       
#   def removeBookShare(self, book_id, target_user):
#     """Removes share to user defined by target_user. Cannot remove owner.
#       Args: book_id - Google Docs spreadsheet identifier
#             target_user - email of target GoogleDOCS account
#       Returns : boolean success      
#     """
#     try:
#       acl_feed = self.fileClient.GetAclPermissions('spreadsheet:'+book_id)
#       for acl in acl_feed.entry:
#         if (acl.scope.value == target_user and acl.role.value != 'owner'):
#           self.fileClient.Delete(acl.GetEditLink().href, force=True)
#           return True
#     except:
#       pass
#     return False
#     
#   def getBookShares(self, book_id, user=None):   
#     """Reads shares on spreadsheet and returns them in list of tuples
#       Args: book_id - Google Docs spreadsheet identifier
#             [user] - email of target GoogleDOCS account
#       Returns : [{user, role}] - list of dictionaries
#     """
#     try:
#       acl_feed = self.fileClient.GetAclPermissions('spreadsheet:'+book_id)
#       ret = []
#       for acl in acl_feed.entry:
#         if user is None:
#           ret.append({'user': acl.scope.value, 'role' : acl.role.value})
#         elif user == acl.scope.value:
#           ret.append({'user': acl.scope.value, 'role' : acl.role.value})
#       return ret
#     except:
#       return []             
    
if __name__ == '__main__':
    pass
  # x = database()
  #y = database('jirka.kulovany@seznam.cz', 'cestlavi3')
  
  # print str(y.getBookShares(book_id='0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c'))
  #book_jirka='0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c'
  #print book_jirka
  #print
  #ret = x.listBooks()
  #for a in ret:
  #  print a['id']
  #print y.createBookShare(book_id=book_jirka, target_user='cvutpoznamky@gmail.com')
  #ret = x.listBooks()
  #for a in ret:
  #  print a['id']
  #print x.deleteBook(book_id=book_jirka)
  #print x.listBooks()
  #print x.getBookShares(book_id=book_jirka)
  #y.removeBookShare(book_id='0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c', target_user='cvutpoznamky@gmail.com')
  #print
  #print x.listBooks()     # spreadsheet:0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c
  #print                                                                             
  # print y.listBooks()     # spreadsheet:0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c
  #print
  # x.getBookShares(book_id='0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c')
  #print
  #print y.getBookShares(book_id='0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c')
  
  
   
  
  # print x.deleteBook(book_id='0AroCJorQndo8dHVRbWtDeWV3MUlkR2d3NUo5WE1ZeEE')
  # print x.listBooks() # '0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c'
  # print x.updateBook(book_id="0AroCJorQndo8dFh5OUVQYV9nZWxJTWdVNWRVUXRGS3c", name="pepa")
  # basic testing
  # print "x.deleteBook(\"omg\"):"    + str(x.deleteBook("omg"))    # True/False (unknown state, clearing the data from last run)
  # tmpBookKey = x.createNewBook(book_name="ffs")
  # print x.createNewBook(book_name="")
  # print x.createNewBook(book_name="franta")
  # print x.listBooks()
  # print "createNote: "+str(x.createNote(book_id='0AqSWlRWYPabFdDhNbDN2NkdYTlpUcExpbFpiU05rYmc', body="cyrdovo testovaci note"))
  # print "getNotesByBookID: " + str(x.getNotesByBookID(book_id=tmpBookKey))
  # print "deleteBook: " + str(x.deleteBook(book_id=tmpBookKey)) 
  # print "x.createNewBook(\"ffs\"):" + str(x.createNewBook("ffs")) # True, does not exist
  # print "x.createNewBook(\"omg\"):" + str(x.createNewBook("omg")) # True, does not exist
  # print "x.createNewBook(\"omg\"):" + str(x.createNewBook("omg")) # False, exists
  # print "x.listBooks():"            + str(x.listBooks())          # ['ffs', 'omg'] 
  # print "x.deleteBook(\"ffs\"):"    + str(x.deleteBook("ffs"))    # True, deleted
  # print "x.listBooks():"            + str(x.listBooks())          # ['omg']
  # print "x.createNote(book_name=\"omg\"):" + str(x.createNote(book_name="omg")) # True
  # print "x.createNote(book_name=\"omg\") with data:" + str(x.createNote(book_name="omg", header='test header', body='test body', tags='test_tag')) # True
  # print "getNotesByBookName \"franta\":"   + str(x.getNotesByBookName(book_name = "franta")) # Empty List for non-existing book
  # print "getNotesByBookName \"omg\":"      + str(x.getNotesByBookName(book_name = "omg")) # List of Dictionaries containing data
  # print "updateNote \"omg\":"              + str(x.updateNote(book_name = "omg", note_id = "0", header = "it works", body = "hello world", tags = "test_note"))
  # print "getNotesByBookName \"omg\":"      + str(x.getNotesByBookName(book_name = "omg")) # Changed data.
  # print "deleteNote from omg with id 1: "  + str(x.deleteNote(book_name = "omg", note_id = "1"))   
  # print "getNotesByBookName \"omg\":"      + str(x.getNotesByBookName(book_name = "omg")) # Changed data.